package com.duleme.dao;

import com.duleme.common.DBUtils;
import com.duleme.entity.Book;
import com.duleme.entity.Order;
import com.duleme.entity.Shop;


import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class OrderDao {
    private Boolean initOrder(Order order){
        String sql = "INSERT INTO orderforgoods (userId,shopId,createTime,deliveryId,total,orderState) VALUES(?,?,?,?,?,?); ";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,order.getUserID());
            statement.setInt(2,order.getShopID());
            statement.setTimestamp(3,new java.sql.Timestamp(order.getCreateTime().getTime()));
            statement.setInt(4,order.getDeliveryID());
            statement.setBigDecimal(5,new BigDecimal(order.getTotal()));
            statement.setInt(6,order.getOrderState());
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public int getOrderIDBytime(Order order){
        String orderSql = "SELECT orderId from orderforgoods where createTime =?;";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement =connection.prepareStatement(orderSql);
            statement.setTimestamp(1,new java.sql.Timestamp(order.getCreateTime().getTime()));
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                int orderID =rs.getInt("orderId");
                DBUtils.close(connection,statement,rs);
                return orderID;
            }
            return -1;
        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    public int getOrderIDbyID(Order order){
        String orderSql = "SELECT orderId from orderforgoods where userId = ? AND shopId =? AND orderState =?;";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement =connection.prepareStatement(orderSql);
            statement.setInt(1,order.getUserID());
            statement.setInt(2,order.getShopID());
            statement.setInt(3,order.getOrderState());
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                int orderID =rs.getInt("orderId");
                DBUtils.close(connection,statement,rs);
                return orderID;
            }
            return -1;
        }catch (Exception e){
            e.printStackTrace();
        }
        return -1;
    }

    public List<Integer> getOrderIDbyUser(Order order){
        String orderSql = "SELECT orderId from orderforgoods where userId = ? AND orderState =?;";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement =connection.prepareStatement(orderSql);
            statement.setInt(1,order.getUserID());
            statement.setInt(2,order.getOrderState());
            ResultSet rs = statement.executeQuery();
            List<Integer> list = new ArrayList<>();
            while (rs.next()){
                int orderID =rs.getInt("orderId");
                //return orderID;
                list.add(orderID);
            }
            DBUtils.close(connection,statement,rs);
            if (list.size()==0){
                return null;
            }else {
                return list;
            }
            //return -1;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public Boolean initOrderItem(Order order){
        if (initOrder(order)) {
            int orderID = getOrderIDbyID(order);
            if (orderID > 0) {
                String sql = "INSERT INTO orders (orderid,bookid,count,subtotal) VALUES (?,?,?,?);";
                List<Book> list = order.getBooks();
                System.out.println("book:" + list.size());
                Map<Integer, Integer> map = new HashMap<>();
                for (Book b : list) {
                    System.out.println(b.getBookName());
                    if (map.containsKey(b.getBookId())) {
                        Integer i = map.get(b.getBookId());
                        i += 1;
                        map.put(b.getBookId(), i);
                    } else {
                        map.put(b.getBookId(), 1);
                    }
                }
                System.out.println("不同的书:" + map.size());
                for (Map.Entry<Integer, Integer> entry : map.entrySet()) {
                    try {
                        BookDao bookDao = new BookDao();
                        Book book = bookDao.findBookByBookId(entry.getKey());
                        Connection connection = DBUtils.getConnection();
                        PreparedStatement statement = connection.prepareStatement(sql);
                        statement.setInt(1, orderID);
                        statement.setInt(2, entry.getKey());
                        statement.setInt(3, entry.getValue());
                        statement.setDouble(4, book.getPrice()* entry.getValue());
                        if (statement.executeUpdate() > 0) {
                            DBUtils.close(connection, statement, null);
                        } else {
                            return false;
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                }
                return true;
            } else {
                return false;
            }
        }else {
            return false;
        }
    }

    public Order getOrder(int orderID,int orderState){
        String sql1 ="SELECT * FROM orderforgoods where orderId = ? and orderState = ?;";
        String sql2 ="SELECT * FROM orders where orderId = ?";
        try {
            Order order =new Order();
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement1 = connection.prepareStatement(sql1);
            statement1.setInt(1,orderID);
            statement1.setInt(2,orderState);
            ResultSet rs1 = statement1.executeQuery();
            if (rs1.next()){
                order.setUserID(rs1.getInt("userId"));
                order.setShopID(rs1.getInt("shopId"));
                order.setOrderState(rs1.getInt("orderState"));
                order.setDeliveryID(rs1.getInt("deliveryId"));
                order.setOrderID(rs1.getInt("orderId"));
                order.setCreateTime(rs1.getTimestamp("createTime"));
            }else {
                return null;
            }
            DBUtils.close(null,statement1,rs1);
            PreparedStatement statement2 = connection.prepareStatement(sql2);
            statement2.setInt(1,orderID);
            ResultSet rs2 = statement2.executeQuery();
            BookDao bookDao = new BookDao();
            while (rs2.next()){
                int length = rs2.getInt("count");
                for (int i=0;i<length;i++){
                    order.addBook(bookDao.findBookByBookId(rs2.getInt("bookid")));
                }
            }
            DBUtils.close(connection,statement2,rs2);
            return order;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public Order getOrder(int orderID){
        String sql1 ="SELECT * FROM orderforgoods where orderId = ?;";
        String sql2 ="SELECT * FROM orders where orderId = ?";
        try {
            Order order =new Order();
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement1 = connection.prepareStatement(sql1);
            statement1.setInt(1,orderID);
            ResultSet rs1 = statement1.executeQuery();
            if (rs1.next()){
                order.setUserID(rs1.getInt("userId"));
                order.setShopID(rs1.getInt("shopId"));
                order.setOrderState(rs1.getInt("orderState"));
                order.setDeliveryID(rs1.getInt("deliveryId"));
                order.setOrderID(rs1.getInt("orderId"));
                order.setCreateTime(rs1.getDate("createTime"));
            }else {
                return null;
            }
            DBUtils.close(null,statement1,rs1);
            PreparedStatement statement2 = connection.prepareStatement(sql2);
            statement2.setInt(1,orderID);
            ResultSet rs2 = statement2.executeQuery();
            BookDao bookDao = new BookDao();
            while (rs2.next()){
                int length = rs2.getInt("count");
                for (int i=0;i<length;i++){
                    order.addBook(bookDao.findBookByBookId(rs2.getInt("bookid")));
                }
            }
            DBUtils.close(connection,statement2,rs2);
            return order;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    private Boolean deleteOrder(Order order){
        String sql ="DELETE FROM orderforgoods WHERE orderId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement =connection.prepareStatement(sql);
            statement.setInt(1,order.getOrderID());
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean delete(Order order){
        if (deleteOrder(order)){
            String sql = "DELETE FROM orders WHERE orderId=?";
            try {
                Connection connection =DBUtils.getConnection();
                PreparedStatement statement =connection.prepareStatement(sql);
                statement.setInt(1,order.getOrderID());
                if (statement.executeUpdate()>0){
                    DBUtils.close(connection,statement,null);
                    return true;
                }
                return false;
            }catch (Exception e){
                e.printStackTrace();
            }
            return false;
        }else {
            return false;
        }

    }

    public Boolean setState(Order order){
        String sql = "UPDATE orderforgoods SET orderState=? WHERE orderId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,order.getOrderState());
            statement.setInt(2,order.getOrderID());
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return true;
    }

    public boolean setAddress(int orderID,int deliveryID){
        String sql = "UPDATE orderforgoods SET deliveryId=? WHERE orderId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,deliveryID);
            statement.setInt(2,orderID);
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return true;
    }

    public List<Integer> getOrderByShop(Shop shop){
        int shopID = shop.getShopId();
        String sql = "SELECT orderId FROM orderforgoods where  shopId = ?";
        List<Integer> list = new ArrayList<>();
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,shopID);
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                list.add(rs.getInt("orderId"));
            }
            DBUtils.close(connection,statement,rs);
            if (list != null){
                return list;
            }
            return null;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args){
        OrderDao orderDao = new OrderDao();
        Order order = new Order();
        /*BookDao bookDao = new BookDao();
        List<Book> list = new ArrayList<>();
        for (int i=0;i<10;i++){
            Book book = bookDao.findBookByBookId(90+i);
            list.add(book);
            order.addBook(book);
        }
        order.setCreateTime(new Date());
        order.setDeliveryID(12);
        order.setOrderState(1);
        order.setShopID(1002);
        order.setUserID(1000012);*/
        //System.out.println(orderDao.initOrderItem(order));
        /*OrderDao orderDao = new OrderDao();
        Order order = new Order();
        order.setOrderID(37);
        System.out.println(orderDao.delete(order));*/
        order.setUserID(100020);
        order.setOrderState(1);
        //List<Integer> list = orderDao.getOrderByShop(shop);
        DBUtils.object2Json(orderDao.getOrderIDbyUser(order));
    }
}
